/*
Created		10/17/2006
Modified		11/9/2006
Project		
Model			
Company		
Author		
Version		
Database		MS SQL 2005 
*/
























Drop table [comment] 
go
Drop table [time_register] 
go
Drop table [recipient] 
go
Drop table [task] 
go
Drop table [dailyreport] 
go
Drop table [usersystem] 
go
Drop table [team] 
go


























Create table [team]
(
	[teamid] Integer Identity(1,1) NOT NULL,
	[parent_teamid] Integer NULL,
	[name] Varchar(200) NULL,
	[managerid] Integer NULL,
Primary Key ([teamid])
) 
go

Create table [usersystem]
(
	[userid] Integer Identity(1,1) NOT NULL,
	[teamid] Integer NOT NULL,
	[firstname] Varchar(250) NULL,
	[lastname] Varchar(250) NULL,
	[user_membershipid] Integer NULL,
Primary Key ([userid])
) 
go

Create table [dailyreport]
(
	[dailyreportid] Integer Identity(1,1) NOT NULL,
	[userid] Integer NOT NULL,
	[starttime] Datetime NOT NULL,
	[endtime] Datetime NULL,
	[problem] Text NULL,
	[suggestion] Text NULL,
	[currentactive] Bit NULL,
Primary Key ([dailyreportid])
) 
go

Create table [task]
(
	[taskid] Integer Identity(1,1) NOT NULL,
	[userid] Integer NOT NULL,
	[title] Varchar(300) NULL,
	[description] Text NULL,
Primary Key ([taskid])
) 
go

Create table [recipient]
(
	[userid] Integer NOT NULL,
	[recipientid] Integer Identity(1,1) NOT NULL,
Primary Key ([recipientid])
) 
go

Create table [time_register]
(
	[time_registerid] Integer Identity(1,1) NOT NULL,
	[dailyreportid] Integer NOT NULL,
	[starttime] Datetime NULL,
	[endtime] Datetime NULL,
	[note] Text NULL,
	[registertype] Integer NULL,
Primary Key ([time_registerid])
) 
go

Create table [comment]
(
	[commentid] Integer Identity(1,1) NOT NULL,
	[time_registerid] Integer NOT NULL,
	[comment] Text NOT NULL,
Primary Key ([commentid])
) 
go

























Alter table [team] add  foreign key([parent_teamid]) references [team] ([teamid])  on update no action on delete no action 
go
Alter table [usersystem] add  foreign key([teamid]) references [team] ([teamid])  on update no action on delete no action 
go
Alter table [recipient] add  foreign key([userid]) references [usersystem] ([userid])  on update no action on delete no action 
go
Alter table [dailyreport] add  foreign key([userid]) references [usersystem] ([userid])  on update no action on delete no action 
go
Alter table [task] add  foreign key([userid]) references [usersystem] ([userid])  on update no action on delete no action 
go
Alter table [team] add  foreign key([managerid]) references [usersystem] ([userid])  on update no action on delete no action 
go
Alter table [time_register] add  foreign key([dailyreportid]) references [dailyreport] ([dailyreportid])  on update no action on delete no action 
go
Alter table [comment] add  foreign key([time_registerid]) references [time_register] ([time_registerid])  on update no action on delete no action 
go


Set quoted_identifier on
go

























Set quoted_identifier off
go











/* Roles permissions */





/* Users permissions */






